package com.union.core.sys.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

import com.union.core.sys.dao.SysModuleDao;
import com.union.core.sys.model.SysModule;
import com.hnjowe.webdb.DBOperation;

/**
 * DAO实现类: 系统模块
 * filename: SysModuleDaoImpl.java
 * project:  CCTR
 * @company Hainan Jowe Technology Ltd
 * @author wangxiuwu
 * @create in 2009-5-21
 * @version 1.0
 */
public class SysModuleDaoImpl implements SysModuleDao {
	
	private DBOperation opera;
	private SysModule sysModule;
	private static Logger log = Logger.getLogger(SysModuleDaoImpl.class.getName());

	public SysModuleDaoImpl() {
	}

	/**
	 * 2.1.1 取出模块列表
	 * @param moduleTypeId int
	 * @return List<ModuleBean>
	 */
	public List<SysModule> getModuleList(int moduleTypeId){

		String sqlsrc = "";
		if (moduleTypeId != 0) {
			sqlsrc = " and a.MODULE_TYPE_ID=" + moduleTypeId;
		}
		List<SysModule> moduleList = new ArrayList<SysModule>();
		try{
			opera = new DBOperation();
			opera.transcationBegin();
	
			String sql = "select "
					+ "a.MODULE_ID,a.MODULE_NAME,a.ORDER_NUM,a.STATUS,a.IF_SHOW,a.ACTION,a.MODULE_TYPE_ID,b.MODULE_TYPE_NAME,a.PARENT_MODULE_ID "
					+ "from sys_module a,sys_module_type b "
					+ "where a.MODULE_TYPE_ID = BINARY b.MODULE_TYPE_ID " + sqlsrc
					+ " order by a.ORDER_NUM";
			opera.prepStatment(sql);
			ResultSet res = opera.executeQuery();
			int i = 0;
			while (res.next()) {
				sysModule = new SysModule();
				sysModule.setMODULE_ID(res.getInt(1));
				sysModule.setMODULE_NAME(res.getString(2));
				sysModule.setORDER_NUM(res.getInt(3));
				sysModule.setSTATUS(res.getInt(4));
				sysModule.setIF_SHOW(res.getInt(5));
				sysModule.setACTION(res.getString(6));
				sysModule.setMODULE_TYPE_ID(res.getInt(7));
				sysModule.setMODULE_TYPE_NAME(res.getString(8));
				sysModule.setParent_module_id(res.getInt(9));
				moduleList.add(i, sysModule);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}

		return moduleList;
	}

	/**
	 * 2.1.2 取出模块列表(取出有效1/无效0状态且是否可见的记录)
	 * @param STATUS int
	 * @param ifShow int
	 * @param moduleTypeId int
	 * @return List<ModuleBean>
	 */
	public List<SysModule> getModuleList(int STATUS, int ifShow, int moduleTypeId){

		String sql_1 = "";
		String sql_2 = "";
		if (moduleTypeId != 0) {
			sql_1 = " and a.MODULE_TYPE_ID=" + moduleTypeId;
		}
		if (ifShow == 2) {
			sql_2 = "";
		} else {
			sql_2 = " and a.IF_SHOW=" + ifShow;
		}

		List<SysModule> moduleList = new ArrayList<SysModule>();
		try{
			opera = new DBOperation();
			opera.transcationBegin();//建立连接并连接事物
	
			String sql = "select "
					+ "a.MODULE_ID,a.MODULE_NAME,a.ORDER_NUM,a.STATUS,a.IF_SHOW,a.ACTION,a.MODULE_TYPE_ID,b.MODULE_TYPE_NAME,a.PARENT_MODULE_ID "
					+ "from sys_module a,sys_module_type b "
					+ "where a.MODULE_TYPE_ID = BINARY b.MODULE_TYPE_ID "
					+ "and a.STATUS=? " + sql_1 + sql_2 + " order by a.ORDER_NUM";
	
			opera.prepStatment(sql);
			opera.setInt(1, STATUS);
			opera.setInt(2, ifShow);
			ResultSet res = opera.executeQuery();//执行上面语句
			int i = 0;
			while (res.next()) {
				sysModule = new SysModule();
				sysModule.setMODULE_ID(res.getInt(1));
				sysModule.setMODULE_NAME(res.getString(2));
				sysModule.setORDER_NUM(res.getInt(3));
				sysModule.setSTATUS(res.getInt(4));
				sysModule.setIF_SHOW(res.getInt(5));
				sysModule.setACTION(res.getString(6));
				sysModule.setMODULE_TYPE_ID(res.getInt(7));
				sysModule.setMODULE_TYPE_NAME(res.getString(8));
				sysModule.setParent_module_id(res.getInt(9));
				moduleList.add(i, sysModule);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return moduleList;
	}

	/**
	 * 2.1.3 根据链接文件名取出对应的模块ID编号
	 * @param action_file String
	 * @return int
	 */
	public int getModuleId(String action_file){
		int ModuleId = 0;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
	
			String sql = "select MODULE_ID " + "from sys_module "
					+ "where STATUS=1 and BINARY ACTION like '%" + action_file
					+ "%'";
	
			opera.prepStatment(sql);
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				ModuleId = res.getInt(1);
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return ModuleId;
	}

	/**
	 * 2.2 添加模块数据
	 * @param moduleBean ModuleBean
	 * @return boolean
	 */
	public boolean addModule(SysModule sysModule) {
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "insert into sys_module"
					+ "(MODULE_NAME,STATUS,IF_SHOW,ACTION,MODULE_TYPE_ID,ORDER_NUM,PARENT_MODULE_ID) "
					+ "values(?,?,?,?,?,?,?)"; 
			opera.prepStatment(sql);
			opera.setString(1, sysModule.getMODULE_NAME());
			opera.setInt(2, sysModule.getSTATUS());
			opera.setInt(3, sysModule.getIF_SHOW());
			opera.setString(4, sysModule.getACTION());
			opera.setInt(5, sysModule.getMODULE_TYPE_ID());
			opera.setInt(6, sysModule.getORDER_NUM());
			opera.setInt(7, sysModule.getParent_module_id());
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	/**
	 * 2.3 删除模块数据
	 * @param moduleId int
	 * @return boolean
	 */
	public boolean deleteModule(int moduleId) {
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from sys_module where MODULE_ID=?";
			opera.prepStatment(sql);
			opera.setInt(1, moduleId);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}
	
	/**
	 * 删除某模块类型下的所有模块
	 * @param moduleTypeId
	 * @return boolean
	 */
	public boolean deleteModuleEx(int moduleTypeId) {
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from sys_module where MODULE_TYPE_ID=?";
			opera.prepStatment(sql);
			opera.setInt(1, moduleTypeId);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	/**
	 * 2.4.1 修改模块数据1
	 * @param moduleBean ModuleBean
	 * @return boolean
	 */
	public boolean modifyModule(SysModule sysModule) {
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
	
			String sql = "update sys_module "
					+ "set MODULE_NAME=?,STATUS=?,ACTION=?,MODULE_TYPE_ID=? "
					+ "where MODULE_ID=?";
			opera.prepStatment(sql);
			opera.setString(1, sysModule.getMODULE_NAME());
			opera.setInt(2, sysModule.getSTATUS());
			opera.setString(3, sysModule.getACTION());
			opera.setInt(4, sysModule.getMODULE_TYPE_ID());
			opera.setInt(5, sysModule.getMODULE_ID());
			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	/**
	 * 2.4.2 修改模块数据2
	 * 修改模块状态
	 * @param moduleId int
	 * @param STATUS int
	 * @return boolean
	 */
	public boolean modifyModule(int moduleId, int STATUS) {
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
	
			String sql = "update sys_module " 
					+ "set STATUS=? "
					+ "where MODULE_ID=?";
			opera.prepStatment(sql);
			opera.setInt(1, STATUS);
			opera.setInt(2, moduleId);
			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	/**
	 * 2.4.3 修改模块数据3
	 * 修改是否可见状态
	 * @param moduleId int
	 * @param ifShow int
	 * @return boolean
	 */
	public boolean modifyModule3(int moduleId, int ifShow) {
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();//建立连接并连接事物
	
			String sql = "update sys_module " 
					+ "set IF_SHOW=? "
					+ "where MODULE_ID=?";
			opera.prepStatment(sql);
			opera.setInt(1, ifShow);
			opera.setInt(2, moduleId);
			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}
	
	/**
	 * 提取信息
	 * @param MODULE_ID int
	 * @return ModuleBean
	 */
	public SysModule getModule(int MODULE_ID) {

		SysModule sysModule = null;
		try {
			opera = new DBOperation();
			opera.transcationBegin();

			String sql = "select "
				+ "a.MODULE_ID,a.MODULE_NAME,a.ORDER_NUM,a.STATUS,a.IF_SHOW,a.ACTION,a.MODULE_TYPE_ID,b.MODULE_TYPE_NAME,a.PARENT_MODULE_ID "
				+ "from sys_module a,sys_module_type b "
				+ "where a.MODULE_ID=? and a.MODULE_TYPE_ID = BINARY b.MODULE_TYPE_ID";
			opera.prepStatment(sql);
			opera.setInt(1, MODULE_ID);
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				sysModule = new SysModule();
				sysModule.setMODULE_ID(res.getInt(1));
				sysModule.setMODULE_NAME(res.getString(2));
				sysModule.setORDER_NUM(res.getInt(3));
				sysModule.setSTATUS(res.getInt(4));
				sysModule.setIF_SHOW(res.getInt(5));
				sysModule.setACTION(res.getString(6));
				sysModule.setMODULE_TYPE_ID(res.getInt(7));
				sysModule.setMODULE_TYPE_NAME(res.getString(8));
				sysModule.setParent_module_id(res.getInt(9));
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return sysModule;
	}
	

/*--------------------------------------------------------------------*/
	/**
	 * 模块排序
	 * @param ModuleId int
	 * @param Type String
	 * @param ModuleTypeId int
	 * @throws Exception
	 */
	public void orderModule(int ModuleId, String Type, int ModuleTypeId)
			throws Exception {

		int pos = 0; //要排序栏目排序前所处位置
		int uppos = 0; //上一位置
		int downpos = 0; //下一位置

		List<SysModule> moduleList = getModuleList(ModuleTypeId);
		int size = moduleList.size();//模块类型个数
		for (int i = 0; i < size; i++) {
			SysModule moduleBean = (SysModule)moduleList.get(i);
			if (ModuleId == moduleBean.getMODULE_ID()) {
				pos = i;//记录位置
				break;
			}
		}
		if ((size == 0) || (size == 1)) {//不能进行排序
			return;

		} else if (size == 2) {
			if (pos == 0) {
				uppos = pos;
				downpos = 1;
			} else {
				uppos = 0;
				downpos = pos;
			}

		} else {//栏目个数>=2
			if (pos == 0) { //在首部，只有降，无升
				uppos = pos = 0;
				downpos = 1;
			} else if (pos == size - 1) {//在末尾，只有升，无降
				uppos = pos - 1;
				downpos = pos;
			} else {
				uppos = pos - 1; //升一位
				downpos = pos + 1; //降一位
			}
		}

		SysModule moduleBean;
		SysModule moduleBean_up;
		SysModule moduleBean_down;

		moduleBean = (SysModule) moduleList.get(pos);
		moduleBean_up = (SysModule) moduleList.get(uppos);
		moduleBean_down = (SysModule) moduleList.get(downpos);

		int moduleId = moduleBean.getMODULE_ID();
		int order_num = moduleBean.getORDER_NUM();

		if (Type.equals("up")) {//升
			int moduleId_up = moduleBean_up.getMODULE_ID();
			int order_num_up = moduleBean_up.getORDER_NUM();

			changeOrderNum_module(moduleId, order_num_up);
			changeOrderNum_module(moduleId_up, order_num);

		} else {//降
			int moduleId_down = moduleBean_down.getMODULE_ID();
			int order_num_down = moduleBean_down.getORDER_NUM();

			changeOrderNum_module(moduleId, order_num_down);
			changeOrderNum_module(moduleId_down, order_num);
		}
	}


	/**
	 * 自动获取排列序号
	 * @return int
	 */
	public int getOrderNumByAuto_module() {
		int OrderNum = 1;
		try {
			List<Integer> OrderNumList = getOrderNumList_module();
			int size = OrderNumList.size();
			if (size == 0) {
				OrderNum = 1;
			} else {
				OrderNum = (Integer)OrderNumList.get(0) + 1;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return OrderNum;
	}	
	
	/**
	 * 取出序号清单
	 * @return List<Integer>
	 * @throws Exception
	 */
	public List<Integer> getOrderNumList_module() throws Exception {

		List<Integer> OrderNumList = new ArrayList<Integer>();
		opera = new DBOperation();
		opera.transcationBegin();//建立连接并连接事物
	    String sql = "select "
            		+ "ORDER_NUM "
            		+ "from sys_module "
            		+ "order by ORDER_NUM desc";
		opera.prepStatment(sql);
		ResultSet res = opera.executeQuery();//执行上面语句
		int i = 0;
		while (res.next()) {
			OrderNumList.add(i,res.getInt(1));
			i++;
		}
		opera.closePres();
		opera.closeConn();
		return OrderNumList;
	}
	
	/**
	 * 改变排序号
	 * @param moduleId int
	 * @param newOrderNum int
	 * @return boolean
	 */
	public boolean changeOrderNum_module(int moduleId, int newOrderNum) {
		boolean result = false;
		opera = new DBOperation();
		opera.transcationBegin();
		String sql = "update sys_module " 
				+ "set order_num=? "
				+ "where MODULE_ID=?";
		opera.prepStatment(sql);
		opera.setInt(1, newOrderNum);
		opera.setInt(2, moduleId);

		opera.executeUpdate();
		result = opera.transcationCommit();

		opera.closePres();
		opera.closeConn();
		return result;
	}
	
}
